﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using System.Data.Common;
using WebUI.DataLayer.Library;
using WebUI.Library;
using Katilgan.Orm;
using System.Configuration;

namespace Katilgan.ORM
{
    public class DataContext<TEntity> where TEntity : class, new()
    {
        public DataContext()
        {
            this.ConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            this.ProviderName = ConfigurationManager.ConnectionStrings["DefaultConnection"].ProviderName;
        }

        public string ConnectionString { get; internal set; }
        public string ProviderName { get; internal set; }

        private DbProviderFactory dbProvider { get { return DbProviderFactories.GetFactory(this.ProviderName); } }
        private DbConnection CreateConnection()
        {
            DbConnection con = dbProvider.CreateConnection();
            con.ConnectionString = this.ConnectionString;
            return con;
        }
        private DbParameter CreateParameter(string paramName, object value)
        {
            DbParameter param = dbProvider.CreateParameter();
            param.ParameterName = paramName;
            param.Value = value;
            return param;
        }

        public TE RunCommand<TE>(Func<DbCommand, TE> dbExecute)
        {
            return RunCommand<TE>(dbExecute, this.ConnectionString);
        }

        public TE RunCommand<TE>(Func<DbCommand, TE> dbExecute, string conStr)
        {
            DbConnection con = this.CreateConnection();
            DbCommand cmd = con.CreateCommand();
            try
            {
                con.Open();
                return dbExecute(cmd);
            }
            catch (Exception e)
            {
                throw new Exception("DB Hatası", e);
            }
            finally
            {
                con.Close();
            }
        }

        public List<TEntity> GetByExample(TEntity entity)
        {
            return RunCommand(cmd =>
            {
                cmd.CommandText = GetByExampleQry(entity);
                return MapEntity(cmd.ExecuteReader());
            });
        }

        public List<TEntity> GetAllData(string orderByColumn = "", params string[] columnNames)
        {
            return RunCommand(cmd =>
            {
                cmd.CommandText = GetSelectQry(columnNames) + (orderByColumn == "" ? "" : " ORDER BY " + orderByColumn);
                return MapEntity(cmd.ExecuteReader());
            });
        }

        public bool UpdateEntity(TEntity entity)
        {
            return RunCommand(cmd =>
            {
                return GetUpdateQry(entity, cmd).ExecuteNonQuery() > 0;
            });
        }

        public bool InsertEntity(TEntity entity)
        {
            return RunCommand(cmd =>
            {
                object identityValue = GetInsertQry(entity, cmd).ExecuteScalar();
                var propInfo = entity.GetType().GetProperties().Where(pInfo => pInfo.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Length > 0).FirstOrDefault();
                if (propInfo != null && (propInfo.GetValue(entity, null) == null || ((int)propInfo.GetValue(entity, null)) == 0))
                {
                    propInfo.SetValue(entity, Convert.ChangeType(identityValue, Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType), null);
                }
                return true;
            });
        }

        public DbCommand GetUpdateQry(TEntity entity, DbCommand cmd)
        {
            if (entity is BaseEntity) { (entity as BaseEntity).IsSaving = true; }

            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("Update {0} Set ", typeof(TEntity).Name);

            StringBuilder whereBuilder = new StringBuilder(" WHERE ");
            foreach (PropertyInfo info in typeof(TEntity).GetProperties().Where(p => p.GetSetMethod() != null && p.GetCustomAttributes(typeof(NonDbColumnAttribute), false).Count() <= 0))
            {
                object dataValue = info.GetValue(entity, null);
                if (info.GetCustomAttributes(typeof(PrimaryKeyAttribute), false).Count() <= 0 && dataValue != null)
                {
                    sb.AppendFormat(" {0} = @{0} ,", info.Name);
                    cmd.Parameters.Add(this.CreateParameter(info.Name, dataValue));
                }
                else if (dataValue != null)
                {
                    whereBuilder.AppendFormat(" {0} = @{0} AND", info.Name);
                    cmd.Parameters.Add(this.CreateParameter(info.Name, dataValue));
                }
            }
            string query = sb.ToString();
            query = query.Remove(query.LastIndexOf(","), 1);
            string where = whereBuilder.ToString();
            where = where.Remove(where.LastIndexOf("AND"), 3);


            cmd.CommandText = query + where;

            return cmd;
        }
        public DbCommand GetInsertQry(TEntity entity, DbCommand cmd)
        {
            if (entity is BaseEntity) { (entity as BaseEntity).IsSaving = true; }

            string columns = "";
            string parameters = "";
            foreach (PropertyInfo info in typeof(TEntity).GetProperties().
                Where(p => p.GetSetMethod() != null
                        && p.GetCustomAttributes(typeof(NonDbColumnAttribute), false).Count() <= 0
                        && (p.GetCustomAttributes(typeof(PrimaryKeyAttribute), false) as PrimaryKeyAttribute[]).ToList().Where(pr => pr.IsIdentity).Count() <= 0
                        )
                )
            {
                object columnValue = info.GetValue(entity, null);
                if (columnValue != null)
                {
                    columns += string.Format("{0},", info.Name);
                    parameters += string.Format(" @{0} ,", info.Name);
                    cmd.Parameters.Add(this.CreateParameter(info.Name, columnValue));
                }
            }

            StringBuilder querySb = new StringBuilder();
            querySb.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", typeof(TEntity).Name, columns.Remove(columns.LastIndexOf(","), 1), parameters.Remove(parameters.LastIndexOf(","), 1));

            cmd.CommandText = querySb.ToString() + "; SELECT SCOPE_IDENTITY();";

            return cmd;
        }

        private int ExecuteScalarQuery(DbCommand cm)
        {
            SqlConnection con = new SqlConnection(this.ConnectionString);
            cm.Connection = con;

            con.Open();

            int result = Int32.Parse(cm.ExecuteScalar().ToString());

            con.Close();

            return result;
        }

        public string GetSelectQry(params string[] columnNames)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select ");
            if (columnNames.Length > 0)
            {
                for (int i = 0; i < columnNames.Length; i++)
                {
                    sb.Append(columnNames[i]);
                    if (i != columnNames.Length - 1) { sb.Append(","); }
                }
            }
            else { sb.Append(" * "); }

            sb.AppendFormat(" From {0}", typeof(TEntity).Name);
            return sb.ToString();
        }

        public string GetDistinctSelectQry(int MaxRow, params string[] columnNames)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append("Select distinct ");

            if (MaxRow > 0)
            {
                sb.Append(" top " + MaxRow.ToString() + " ");
            }
            if (columnNames.Length > 0)
            {
                for (int i = 0; i < columnNames.Length; i++)
                {
                    sb.Append(columnNames[i]);
                    if (i != columnNames.Length - 1) { sb.Append(","); }
                }
            }
            else { sb.Append(" * "); }

            sb.AppendFormat(" From {0}", typeof(TEntity).Name);
            return sb.ToString();
        }


        private string GetByExampleQry(TEntity entity)
        {
            StringBuilder sb = new StringBuilder(GetSelectQry());
            sb.Append(" WHERE ");
            foreach (PropertyInfo info in typeof(TEntity).GetProperties().Where(p => p.GetCustomAttributes(typeof(NonDbColumnAttribute), false).Count() <= 0))
            {
                object propertyValue = info.GetValue(entity, null);
                if (propertyValue != null && propertyValue.ToString() != "0")
                {
                    string surroundChar = info.PropertyType == typeof(string) ? "'" : string.Empty;
                    sb.AppendFormat("( {0}={2}{1}{2} ) AND ", info.Name, propertyValue, surroundChar);
                }
            }
            string query = sb.ToString();
            query = query.Remove(query.LastIndexOf("AND"));

            return query;
        }

        public List<TEntity> MapEntity(IDataReader dr, params string[] exceptColumns)
        {
            List<TEntity> entityList = new List<TEntity>();
            while (dr.Read())
            {
                TEntity entity = new TEntity();
                var props = typeof(TEntity).GetProperties();
                foreach (PropertyInfo info in props)
                {
                    foreach (DataRow row in dr.GetSchemaTable().Rows)
                    {
                        if (row[0].ToString().ToLowerInvariant() == info.Name.ToLowerInvariant())
                        {
                            info.SetValue(entity, FormatHelper.AssignValue(dr[info.Name], Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType, !exceptColumns.Contains(info.Name)), null);
                        }
                    }
                }
                entityList.Add(entity);
            }
            return entityList;
        }

        public DataTable MapToDataTable(IDataReader sdr, params string[] exceptColumns)
        {
            DataTable ret = new DataTable();
            DataTable schema = sdr.GetSchemaTable();
            for (int n = 0; n < schema.Rows.Count; n++)
            {
                ret.Columns.Add(new DataColumn((String)schema.Rows[n]["ColumnName"], (Type)schema.Rows[n]["DataType"]));
            }
            while (sdr.Read())
            {
                DataRow dr = ret.NewRow();
                for (int n = 0; n < sdr.FieldCount; n++)
                {

                    dr[n] = FormatHelper.AssignValue(sdr.GetValue(n), (schema.Rows[n]["DataType"] as Type), !exceptColumns.Contains(schema.Rows[n]["ColumnName"]), true);
                }
                ret.Rows.Add(dr);
            }
            return ret;
        }
    }
}
